package gestion.dao;

import gestion.conexion.ConexionBD;
import gestion.modelo.Usuario;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

 
 


public class UsuarioDAO extends BaseDAO {

	public Usuario Insertar (Usuario u){
		
		String query = "insert into persona ( " +
				"   perfil_idperfil,	" +
				"   centro_informacion_idcentro_informacion ," +
				"	nombre 	," +
				"	paterno 	," +
				"	materno 	," +
				"	sexo 	," +
				"	tipo_documento ," +
				"	nro_documento 	," +
				"	correo 	," +
				"	celular 	," +
				"	clave	 ) values (1,1,?,?,?,?,?,?,?,?,?) ";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try {
			conn = ConexionBD.obtenerConexion();
			pstm = conn.prepareStatement(query);
			
			pstm.setString(1, u.getNombres());
			pstm.setString(2, u.getPaterno());
			pstm.setString(3, u.getMaterno());
			pstm.setString(4, String.valueOf(u.getSexo()));
			
			pstm.setString(5, u.getTipoDocumento());
			pstm.setString(6, u.getNumeroDocumento());
			pstm.setString(7, u.getCorreo());
			pstm.setString(8, u.getCelular());
			pstm.setString(9, u.getClave());
			
						
		 pstm.executeUpdate();	
		
			
			
		} catch (SQLException e) {
	System.out.println(e.getMessage());
	
		}finally{
			this.cerrarConexion(conn);
			this.cerrarResultSet(rs);
			this.cerrarStatement(pstm);
			
					
		}
		
		return u;
	}
	
	
	
	/*
	 * Actualizar usuario 
	 */
	
	
 
 
	
	public Usuario actualizar(Usuario u) {
		
		String query = "update persona set " +
				"perfil_idperfil=?," +
				"centro_informacion_idcentro_informacion=?," +
				"nombre=?," +
				"paterno=?," +
				"materno=?," +
				"sexo=?," +
				"tipo_documento=?," +
				"nro_documento=?," +
				"correo=?," +
				"celular=?," +
				"clave=?" +
				" where idpersona=?";


		
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = ConexionBD.obtenerConexion();
			stmt = conn.prepareStatement(query);
			
			stmt.setInt(1, u.getPerfil_idperfil());
			
			stmt.setInt(2, u.getCentro_informacion_idcentro_informacion());
			
			stmt.setString(3, u.getNombres());
			stmt.setString(4, u.getPaterno());
			stmt.setString(5, u.getMaterno());
		 
			stmt.setString(6, String.valueOf(u.getSexo()));
			
			stmt.setString(7, u.getTipoDocumento());
			stmt.setString(8, u.getNumeroDocumento());
			stmt.setString(9, u.getCorreo());
			stmt.setString(10, u.getCelular());
			stmt.setString(11, u.getClave());
			stmt.setInt(12, u.getIdpersona());
 
			int i = stmt.executeUpdate();
			
			if (i != 1) {
				throw new SQLException("No se pudo actualizar");
			}else{
				System.out.println("Actualizado correctamente");
			}
			
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			
				}finally{
					this.cerrarConexion(conn);
					this.cerrarResultSet(rs);
					this.cerrarStatement(stmt);
					
							
				}
				
				return u;
			}
		
	 /*
	  * Listar de Usuario
	  */

 
	
	
	public Collection<Usuario> listar() throws Exception{
		
		Collection<Usuario> u = new ArrayList<Usuario>();
		
		
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			String query = "select * from persona order by nombre";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Usuario usu = new Usuario();
				
				usu.setIdpersona(rs.getInt("idpersona"));
				usu.setNombres(rs.getString("nombre"));
				usu.setPaterno(rs.getString("paterno"));
				usu.setMaterno(rs.getString("materno"));
				usu.setCorreo(rs.getString("correo"));
				
				usu.setNumeroDocumento(rs.getString("nro_documento"));
				u.add(usu);
			} 

			/*
			if (rs.next()){
			Integer numero = rs.getInt(0);
			}
			*/
			
			
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new Exception();
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		
		return u;
		
		
	}
	
	/*
	 * Buscar una persona
	 */
	
	public Usuario buscar(int idpersona) throws Exception {
		Usuario vo = new Usuario();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			String query = "select idpersona,nombre,correo from persona where idpersona=?";
			
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, idpersona);
			
			rs = stmt.executeQuery();
			
			if (rs.next()) {
				
				vo.setIdpersona(rs.getInt("idpersona"));				
				vo.setNombres(rs.getString("nombre"));
				vo.setCorreo(rs.getString("correo"));
				
			}
			
			 
			
			
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			
			throw new Exception();
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}
	
	
 /*
  * buscar por nombre
  */
	
	
	public Collection<Usuario> buscarPorNombre(String nombre)
			throws Exception {
		String query = "select nombre, paterno, materno, sexo from persona where nombre like ?";
		
		Collection<Usuario> lista = new ArrayList<Usuario>();
		
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			
			stmt.setString(1, "%" + nombre + "%");
			
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				
				Usuario vo = new Usuario();
 
				
				vo.setNombres(rs.getString("nombre"));
				vo.setPaterno(rs.getString("paterno"));
				vo.setMaterno(rs.getString("materno"));
				
				//vo.setSexo(rs.getChar("sexo",0)); 
				vo.setSexo((rs.getString("sexo").toString()).charAt(0));
			
				
				
				lista.add(vo);
				
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new Exception(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		System.out.println(lista.size());
		return lista;
	}
	
	
	/*
	 * Eliminar usuario
	 */
	
	
	public void eliminar(int idPersona) throws Exception{
		
		String query = "delete from persona WHERE idpersona=?";
		
		Connection con = null;
		PreparedStatement stmt = null;
		
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			
			stmt.setInt(1, idPersona);
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo eliminar");
			}
		} catch (Exception e) {
			System.err.println(e.getMessage());
			throw new Exception(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}
	
	
	public Usuario Logeo(Usuario us){
		
	Connection conn = null;
	PreparedStatement pstm= null;
	ResultSet rs = null;
	String  qery= "Select perfil_idperfil, nombre, correo, clave from persona where correo = ? and clave = ?";
		
		try {
			
			conn = ConexionBD.obtenerConexion();
			pstm = conn.prepareStatement(qery);
			pstm.setString(1, us.getCorreo());
	        pstm.setString(2, us.getClave());
	        rs = pstm.executeQuery();
	        
	        
	        if (rs.next()){
	        us.setPerfil_idperfil(rs.getInt(1));
	        us.setNombres(rs.getString(2));
	        us.setCorreo(rs.getString(3));
	        us.setClave(rs.getString(4));
	        }else
	        {
	        us.setCorreo(null);
	        us.setClave(null);
	        	
	        }
	        
			
			
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			cerrarConexion(conn);
			cerrarResultSet(rs);
			cerrarStatement(pstm);
			
		}
		
		
		
		
		
	return us;	
	}
	
	
	
}
